import pandas as pd
import numpy as np

# 1、数据加载， pd.read_excel('./18级高一体测成绩汇总.xls')默认加载第一个工作表
data1 = pd.read_excel('./18级高一体测成绩汇总.xls')
# 2、数据加载， pd.read_excel('./18级高一体测成绩汇总.xls',sheet_name = 1)指定加载第二个工作表
data2 = pd.read_excel('./18级高一体测成绩汇总.xls', sheet_name=1)
# 3、评分标准加载，pd.read_excel('./体侧成绩评分表.xls',header = [0,1])，header=[0,1]表示多层列索引
data3 = pd.read_excel('./体侧成绩评分表.xls', header=[0, 1])
# 4、数据类型转换
#
#  男1000米跑，数据类型是str，并且是4’26这种形式，需要变成float类型的值
data1['男1000米跑'] = data1['男1000米跑'].apply(lambda x: str(x).replace("'", ".")).astype('float64')
# 评分标准中男1000米跑和女800米跑的成绩都是4‘10’‘这种形式，需要转化为float类型值
data3['男1000米跑', '成绩'] = data3['男1000米跑', '成绩'].apply(lambda x: str(x).replace('"', "").replace("'", ".")).astype(
    'float64')
data3['女800米跑', '成绩'] = data3['女800米跑', '成绩'].apply(lambda x: str(x).replace('"', "").replace("'", ".")).astype(
    'float64')
# 其他所有数值类型的值，都要转换为float类型的值
data1[['男50米跑', '男跳远', '男体前屈', '男引体', '男肺活量', '身高', '体重', 'BMI']] = data1[
    ['男50米跑', '男跳远', '男体前屈', '男引体', '男肺活量', '身高', '体重', 'BMI']].astype('float64')
data2[['女50米跑', '女跳远', '女体前屈', '女仰卧', '女肺活量', '身高', '体重', 'BMI']] = data2[
    ['女50米跑', '女跳远', '女体前屈', '女仰卧', '女肺活量', '身高', '体重', 'BMI']].astype('float64')


# 5、对体测成绩进行分数转换，跑步类（越小越好）；跳远、体前屈（越大越好）
#
#  使用map、apply、transform方法
#
#  列索引重排
#
#  转换之后效果

def get_grades(name):
    return pd.concat([data3[name], data3[name, '成绩'].shift(periods=1).rename('成绩1')], axis=1).replace(np.nan,0)


project = ['男1000米跑', '男50米跑', '男跳远', '男体前屈', '男引体', '男肺活量'
    , '女800米跑', '女50米跑', '女跳远', '女体前屈', '女仰卧', '女肺活量']
grade = {}
for i in project:
    grade[i] = get_grades(i)


def sss(x, name):
    if (x == 0):
        return 0
    this_grade = grade[name]
    if str.find(name, '跑') != -1:
        if x >= this_grade['成绩'].max():
            return 0
        elif x <= this_grade['成绩'].min():
            return 100
        return this_grade[(this_grade['成绩'] >= x) & (this_grade['成绩1'] < x)]['分数'].iloc[0]
    else:
        if x >= this_grade['成绩'].max():
            return 100
        elif x <= this_grade['成绩'].min():
            return 0
        return this_grade[(this_grade['成绩'] <= x) & (this_grade['成绩1'] > x)]['分数'].iloc[0]


for i in project:
    print(i)
    if str.find(i, '男') != -1:
        data1[i+'分数'] = data1[i].apply(sss, args=( i,))
    else:
        data2[i+'分数'] = data2[i].apply(sss, args=( i,))
print(data1)
print(data2)
